{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from snownlp import SnowNLP\n",
    "import pymysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "outputs": [],
   "source": [
    "def getConnAndCursor():\n",
    "     try:\n",
    "        conn = pymysql.connect(host=\"localhost\",\n",
    "                           port = 3306,\n",
    "                           user = \"root\",\n",
    "                           password = \"1234\",\n",
    "                           db = \"bc\")\n",
    "        cursor = conn.cursor()\n",
    "        return (conn ,cursor)\n",
    "     except Exception as e:\n",
    "         print(\"链接出错了\", e)\n",
    "         return None"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "outputs": [],
   "source": [
    "def closeConnAndCursor(conn, cursor):\n",
    "    conn.close\n",
    "    cursor.close"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "outputs": [],
   "source": [
    "def getOidData():\n",
    "    try:\n",
    "        conn = pymysql.connect(host=\"localhost\",\n",
    "                           port = 3306,\n",
    "                           user = \"root\",\n",
    "                           password = \"1234\",\n",
    "                           db = \"bc\")\n",
    "        cursor = conn.cursor()\n",
    "        sql = \"\"\"\n",
    "            select oid from hot_content;\n",
    "        \"\"\"\n",
    "        cursor.execute(sql)\n",
    "        oids = cursor.fetchall()\n",
    "        return [oid[0] for oid in oids]\n",
    "    except Exception as e:\n",
    "        print(\"出错了\", e)\n",
    "    finally:\n",
    "        if conn:\n",
    "            conn.close()\n",
    "        if cursor:\n",
    "            cursor.close()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "outputs": [],
   "source": [
    "def getLimitReviewData(tableName):\n",
    "    conn, cursor = getConnAndCursor()\n",
    "    sql = \"\"\"\n",
    "        SELECT * FROM `bc`.`{}` LIMIT 0,1\n",
    "    \"\"\".format(tableName)\n",
    "    cursor.execute(sql)\n",
    "    data = cursor.fetchall()\n",
    "    closeConnAndCursor(conn, cursor)\n",
    "    return data"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "outputs": [],
   "source": [
    "def getEmotionValue(content):\n",
    "    s = SnowNLP(content)\n",
    "    return s.sentiments\n"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "outputs": [],
   "source": [
    "def getAllReviewData(tableName):\n",
    "    conn, cursor = getConnAndCursor()\n",
    "    sql = \"\"\"\n",
    "        SELECT * FROM `bc`.`{}`\n",
    "    \"\"\".format(tableName)\n",
    "    cursor.execute(sql)\n",
    "    data = cursor.fetchall()\n",
    "    closeConnAndCursor(conn, cursor)\n",
    "    return data\n"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "outputs": [],
   "source": [
    "def setEmotionValue(tableName, emotionValue, rpid):\n",
    "    conn, cursor = getConnAndCursor()\n",
    "    sql = \"\"\"\n",
    "        UPDATE `bc`.`{}` SET `emotion_value` = {} WHERE `rpid` = '{}'\n",
    "    \"\"\".format(tableName, emotionValue, rpid)\n",
    "    cursor.execute(sql)\n",
    "    conn.commit()\n",
    "    closeConnAndCursor(conn, cursor)\n",
    "\n",
    "\n"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "outputs": [],
   "source": [
    "def set_emotion_value():\n",
    "    count = 0\n",
    "    for oid in oids:\n",
    "        data4 = getAllReviewData(oid)\n",
    "        count += 1\n",
    "        for d in data4:\n",
    "            emotionvalue = getEmotionValue(d[5])\n",
    "            setEmotionValue(oid, emotionvalue, d[0])\n",
    "        print(count)\n",
    "\n",
    "    print(\"加载完成！\")"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# UPDATE `bc`.`205396001` SET `emotion_value` = 0.2342342 WHERE `rpid` = '4513820016'\n",
    "\n",
    "setEmotionValue(205396001, 0.2342342, 4513820016)\n",
    "print(\"成功\")"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "\n",
    "oids = getOidData()\n",
    "for oid in oids:\n",
    "    print(oid)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "for oid in oids:\n",
    "    data1 = getLimitReviewData(oid)\n",
    "    print(data1)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "for oid in oids:\n",
    "    data2 = getLimitReviewData(oid)\n",
    "    print(data2[0][5])"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "for oid in oids:\n",
    "    data3 = getLimitReviewData(oid)\n",
    "    print(getEmotionValue(data3[0][5]), data3[0][5])"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['4508545030', '青铜门卫小道士', '男', 9126, 6, '唐探三就很烂，导演跳出来说观众不懂！都第三部了还要整谜语！', 0.9932399924680253]\n",
      "['4508421669', '加菲猫肥不肥', '男', 9339, 5, '《让子弹飞》什么时间申遗啊[脱单doge]', 0.8218790157331824]\n",
      "['4515914471', '内位说了', '保密', 1749, 4, '腾讯还把人人字幕搞死了，腾讯不止自己拍的电视剧不行，翻译能力也是垃圾。非常明显的就是《真探》第一季，腾讯还没买版权时候我下载的人人字幕的，翻译的非常好。等到腾讯版的我一看翻译的什么东西，把一些经典的地方全都翻译的变味了，表达不出原来的意思。这么好的剧在国内没火跟腾讯的翻译有很大原因。', 0.07583033997693511]\n",
      "['4508569717', 'GODmt星君', '保密', 16325, 5, '有一说一那些烂剧，没人吐槽我连知道都不知道[辣眼睛][辣眼睛]', 0.42055766588316235]\n",
      "['4509630381', '夙荀', '女', 17185, 5, '懂了，如果影视区集体被冲了，各位的出路和转型有了：轩邈：相声小品，语言类节目表演者刘哔：动画区重制up，猫和老鼠重编编剧鸭子：美妆区up,出吐槽影视的仿妆老邪、炮哥：小号新业务以及各位会成为绘画区新秀。所以，为了不让你们转型，我会参与骂战，希望资方不要太得寸进尺。', 0.8747548785776684]\n",
      "['4509642191', 'beautifulripeng', '保密', 19275, 5, '实话说很多影视剧都是因为up们的二创引流才去看的，比如《隐秘的角落》《沉默的真相》。很多剧最开始没有钱重金做宣发，选角也不是用的流量艺人，能够大爆影视区up解读引流功不可没。烂剧不管如何吐槽人们该不看的还是不会看，并不是因为被吐槽了才成为烂剧的。自己家孩子考试不及格不能怪给孩子写评语的老师吧！影视up是难得好剧烂剧不一边倒的质检员，现在好了，要把这些人sha了，自己跑步自己做裁判。内娱真是要完呀！', 0.9999765545142815]\n",
      "['4511442874', '阿拉德官方冒险家', '男', 442, 5, '现在都不敢批评别人，点个外卖超时半小时没送到，还不敢给差评，就怕人家说啊～人家外卖小哥多累啊，多危险啊，操，老子吃着凉透的米饭，吃完还得上班。如果批评消失，只能有赞美。吃亏的永远是消费者。', 1.9865541489028082e-05]\n",
      "['4512127228', '写书的笔', '男', 2668, 5, '当年《闪光少女》《白蛇缘起》《龙之谷》《魁拔》，要不是大家猛的宣传，早就没落了', 0.9999121024723537]\n",
      "['4511998047', 'raja_童夏', '保密', 7540, 5, '不会真的有人以为这波是重视版权保护吧，他们自己那些抄袭的影视剧综艺怎么说？', 0.005442571515742234]\n",
      "['4508879971', 'Queeniein', '保密', 19611, 5, '我觉得我太幸运了，小时候吃多了山珍海味。长大了起码能分辨出来好坏。最可怕的是那些从小就被喂垃圾长大的小孩。以后就算有山珍海味放到他们面前可能还是只会选择吃垃圾了。', 0.9575322580196363]\n"
     ]
    }
   ],
   "source": [
    "count = 0\n",
    "for oid in oids:\n",
    "    data4 = getAllReviewData(oid)\n",
    "    for d in data4:\n",
    "        d = list(d)\n",
    "        d[6] = getEmotionValue(d[5])\n",
    "        print(d)\n",
    "        count += 1\n",
    "        if count == 10:\n",
    "            break\n",
    "    break\n"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "outputs": [],
   "source": [
    "def getMeanOfEmotion(tableName):\n",
    "    conn, cursor = getConnAndCursor()\n",
    "    sql = \"\"\"\n",
    "        SELECT SUM(emotion_value) /COUNT(1) FROM `{}`\n",
    "    \"\"\".format(tableName)\n",
    "    cursor.execute(sql)\n",
    "    data = cursor.fetchall()[0][0]\n",
    "    # print(data)\n",
    "    closeConnAndCursor(conn, cursor)\n",
    "    return data"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "((0.6033041644368246,),)\n"
     ]
    }
   ],
   "source": [
    "getMeanOfEmotion(205396001)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "\n"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}